const { Production, User } = require("../models");
const { failure } = require("./responses");
const { userInfo } = require("./userInfo");
const { Op } = require("sequelize");

// 查找sn 对应的产品信息
async function productionInfo(sn, long, wide, height, beUsed) {
  console.log(
    "查找对应的信息 sn: ",
    sn,
    "long",
    long,
    "wide",
    wide,
    "height",
    height,
    "beUsed",
    beUsed
  );

  try {
    const condition = {
      where: {},
      attributes: { exclude: ["UserId"] },
      include: [
        {
          model: User,
          as: "user",
          attributes: ["id", "username"],
        },
      ],
      order: [["id", "DESC"]],
    };

    if (sn) {
      condition.where.sn = sn;
    }

    // 为长、宽、高添加精确匹配，利用已创建的索引提高查询性能
    if (long) {
      condition.where.long = long.toString();
    }

    if (wide) {
      condition.where.wide = wide.toString();
    }

    if (height) {
      condition.where.height = height.toString();
    }

    if (beUsed) {
      condition.where.beUsed = beUsed.toString();
    }

    // 使用已创建的复合索引优化查询
    const { count, rows } = await Production.findAndCountAll(condition);
    return {
      count,
      rows,
    };
  } catch (error) {
    console.error("Error in productionInfo:", error);
    throw error;
  }
}

// 优化版本的productionInfo，支持更灵活的查询
async function productionInfoOptimized(filters = {}) {
  try {
    const { sn, long, wide, height, beUsed, status, lineName, limit, offset } =
      filters;

    const condition = {
      where: {},
      attributes: { exclude: ["UserId"] },
      include: [
        {
          model: User,
          as: "user",
          attributes: ["id", "username"],
        },
      ],
      order: [["id", "DESC"]],
    };

    // 利用已创建的索引进行精确匹配
    if (sn) {
      condition.where.sn = sn;
    }

    if (long) {
      condition.where.long = long.toString();
    }

    if (wide) {
      condition.where.wide = wide.toString();
    }

    if (height) {
      condition.where.height = height.toString();
    }

    if (beUsed) {
      condition.where.beUsed = beUsed.toString();
    }

    if (status !== undefined) {
      condition.where.status = status.toString();
    }

    if (lineName) {
      condition.where.lineName = lineName;
    }

    // 设置分页参数
    if (limit) {
      condition.limit = Math.min(limit, 100); // 限制最大返回100条记录
    }

    if (offset !== undefined) {
      condition.offset = offset;
    }

    const { count, rows } = await Production.findAndCountAll(condition);
    return {
      count,
      rows,
    };
  } catch (error) {
    console.error("Error in productionInfoOptimized:", error);
    throw error;
  }
}

// 快速查询接口，专门用于高频查询场景，只查询必要字段
async function productionInfoFast(filters = {}) {
  try {
    const { sn, status, beUsed, palletCode } = filters;

    // 检查是否提供了查询条件
    if (!sn && !status && !beUsed && !palletCode) {
      throw new Error("至少需要提供一个查询条件");
    }

    const condition = {
      where: {},
      attributes: [
        "id",
        "sn",
        "long",
        "wide",
        "height",
        "status",
        "beUsed",
        "lineName",
        "createdAt",
      ],
      order: [["id", "DESC"]],
      limit: 50, // 限制快速查询最多返回50条记录
    };

    // 利用已创建的索引进行精确匹配
    if (sn) {
      condition.where.sn = sn;
    }

    if (status !== undefined) {
      condition.where.status = status.toString();
    }

    if (beUsed) {
      condition.where.beUsed = beUsed.toString();
    }

    if (palletCode) {
      condition.where.palletCode = palletCode;
    }

    const rows = await Production.findAll(condition);
    return {
      count: rows.length,
      rows,
    };
  } catch (error) {
    console.error("Error in productionInfoFast:", error);
    throw error;
  }
}

async function putProduction(data) {
  try {
    const productionDetail = await Production.findByPk(data.id);

    if (!productionDetail) {
      return false;
    } else {
      await productionDetail.update(data);
      return true;
    }
  } catch (error) {
    console.error("Error in productionInfo:", error);
    throw error;
  }
}

// 根据id 查找数据
async function getProductionById(id) {
  try {
    // 利用主键索引进行高效查询
    const productionDetail = await Production.findByPk(id);
    if (!productionDetail) {
      throw new NotFound(`ID: ${id}的产品表未找到。`);
    } else {
      return productionDetail;
    }
  } catch (error) {
    console.error("Error in productionInfo:", error);
    throw error;
  }
}

// 根据 托盘码查找数据 - 优化版本，利用已创建的索引
async function getProductionByPalletCode(code) {
  try {
    // 利用palletCode字段的索引（如果已创建）进行高效查询
    const productionDetail = await Production.findAll({
      where: {
        palletCode: code,
        status: "1",
      },
      // 添加限制，避免返回过多数据
      limit: 100,
    });
    if (!productionDetail || productionDetail.length === 0) {
      throw new NotFound(`托盘码: ${code}的产品表未找到。`);
    } else {
      return productionDetail;
    }
  } catch (error) {
    console.error("Error in productionInfo:", error);
    throw error;
  }
}

/**
 * 更新产品
 * @param {*} req
 * @param {*} id
 * @returns
 */
async function updateProductionById(req, id) {
  console.log("更新产品状态");
  try {
    let { user, originname } = await userInfo(req);
    // 利用主键索引进行高效查询
    const productionDetail = await getProductionItem(id);
    // console.log("更新前的产品状态", productionDetail);
    if (productionDetail.status == 1) {
      throw new NotFound("产品已完成，无法修改。");
    }
    const body = {
      long: productionDetail.long,
      wide: productionDetail.wide,
      height: productionDetail.height,
      presetsNum: productionDetail.presetsNum,
      layerNum: productionDetail.layerNum,
      palletizingNum: productionDetail.palletizingNum,
      grippingDirection: productionDetail.grippingDirection,
      leftAndRightFaultToleranceNum:
        productionDetail.leftAndRightFaultToleranceNum,
      faultToleranceBeforeAndAfterNum:
        productionDetail.faultToleranceBeforeAndAfterNum,
      userId: user.id,
      userName: user.username,
      beUsed: productionDetail.beUsed,
      status: 1,
      sn: productionDetail.sn,
      trayCode: productionDetail.trayCode,
      lineName: productionDetail.lineName,
      sizeRuleId: productionDetail.sizeRuleId,
      weight: productionDetail.weight,
      palletCode: productionDetail.palletCode,
      productCode: productionDetail.productCode,
      sono: productionDetail.sono,
      soline: productionDetail.soline,
    };
    await productionDetail.update(body);
    return true;
  } catch (error) {
    return false;
  }
}

/**
 * 更新产品的托盘码
 * @param {*} req
 * @param {*} id
 * @param {*} palletCode
 * @returns
 */
async function updateProductionByIdToPalletCode(req, id, palletCode) {
  console.log("更新产品状态");
  try {
    let { user, originname } = await userInfo(req);
    // 利用主键索引进行高效查询
    const productionDetail = await getProductionItem(id);
    // console.log("更新前的产品状态", productionDetail);
    if (productionDetail.status == 1) {
      throw new NotFound("产品已完成，无法修改。");
    }
    const body = {
      long: productionDetail.long,
      wide: productionDetail.wide,
      height: productionDetail.height,
      presetsNum: productionDetail.presetsNum,
      layerNum: productionDetail.layerNum,
      palletizingNum: productionDetail.palletizingNum,
      grippingDirection: productionDetail.grippingDirection,
      leftAndRightFaultToleranceNum:
        productionDetail.leftAndRightFaultToleranceNum,
      faultToleranceBeforeAndAfterNum:
        productionDetail.faultToleranceBeforeAndAfterNum,
      userId: user.id,
      userName: user.username,
      beUsed: productionDetail.beUsed,
      status: productionDetail.status,
      sn: productionDetail.sn,
      trayCode: productionDetail.trayCode,
      lineName: productionDetail.lineName,
      sizeRuleId: productionDetail.sizeRuleId,
      weight: productionDetail.weight,
      palletCode: palletCode,
      productCode: productionDetail.productCode,
      sono: productionDetail.sono,
      soline: productionDetail.soline,
    };
    await productionDetail.update(body);
    return true;
  } catch (error) {
    return false;
  }
}

/**
 * 获取所有产品数据
 * @param {Object} options - 查询选项
 * @returns {Promise<Array>} 产品数据列表
 */
async function getAllProductions(options = {}) {
  try {
    const { limit, offset, order, where } = options;

    const condition = {
      where: where || {},
      attributes: { exclude: ["UserId"] },
      include: [
        {
          model: User,
          as: "user",
          attributes: ["id", "username"],
        },
      ],
      order: order || [["id", "DESC"]],
    };

    // 限制最大返回记录数，避免大数据量查询
    if (limit) {
      condition.limit = Math.min(limit, 1000); // 最大限制1000条记录
    } else {
      condition.limit = 100; // 默认限制100条记录
    }

    if (offset !== undefined) {
      condition.offset = offset;
    }

    const productions = await Production.findAll(condition);
    return productions;
  } catch (error) {
    console.error("Error in getAllProductions:", error);
    throw error;
  }
}

/**
 * 获取产品总数
 * @param {Object} where - 查询条件
 * @returns {Promise<Number>} 产品总数
 */
async function getProductionsCount(where = {}) {
  try {
    const count = await Production.count({ where });
    return count;
  } catch (error) {
    console.error("Error in getProductionsCount:", error);
    throw error;
  }
}

/**
 * 将所有产品的状态设置为指定值
 * @param {number} status - 要设置的状态值
 * @param {Object} where - 查询条件（可选）
 * @returns {Promise<number>} 更新的产品数量
 */
async function updateAllProductionsStatus(status, where = {}) {
  try {
    const result = await Production.update(
      { status: status.toString() }, // 确保状态值为字符串类型
      { where }
    );

    // result[0] 包含更新的记录数
    const updatedCount = result[0];
    console.log(`成功更新 ${updatedCount} 个产品的状态为 ${status}`);
    return updatedCount;
  } catch (error) {
    console.error("Error in updateAllProductionsStatus:", error);
    throw error;
  }
}

/**
 * 将所有产品的状态设置为0
 * @returns {Promise<number>} 更新的产品数量
 */
async function resetAllProductionsStatus() {
  try {
    return await updateAllProductionsStatus(0);
  } catch (error) {
    console.error("Error in resetAllProductionsStatus:", error);
    throw error;
  }
}

/**
 * 公共方法：查询当前产品
 */
async function getProductionItem(id) {
  // 利用主键索引进行高效查询
  const productionDetail = await Production.findByPk(id);

  if (!productionDetail) {
    throw new NotFound(`ID: ${id}的产品表未找到。`);
  }

  return productionDetail;
}

/**
 * 批量获取产品信息
 * @param {Array} snList - SN码列表
 * @returns {Promise<Array>} 产品数据列表
 */
async function getProductionsBySnList(snList) {
  try {
    if (!Array.isArray(snList) || snList.length === 0) {
      return [];
    }

    // 利用SN字段索引进行高效批量查询
    const productions = await Production.findAll({
      where: {
        sn: {
          [Op.in]: snList,
        },
      },
      attributes: [
        "id",
        "sn",
        "long",
        "wide",
        "height",
        "status",
        "beUsed",
        "lineName",
      ],
      limit: Math.min(snList.length, 1000), // 限制最大返回记录数
    });

    return productions;
  } catch (error) {
    console.error("Error in getProductionsBySnList:", error);
    throw error;
  }
}

module.exports = {
  productionInfo,
  productionInfoOptimized, // 新增优化版本查询方法
  productionInfoFast, // 新增快速查询方法
  putProduction,
  getProductionById,
  updateProductionById,
  getProductionByPalletCode,
  updateProductionByIdToPalletCode,
  getAllProductions,
  getProductionsCount,
  updateAllProductionsStatus,
  resetAllProductionsStatus,
  getProductionsBySnList, // 新增批量查询方法
};
